package com.sq.partner.manager.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.dao.INlifeCoreCovDao;
import com.sq.partner.manager.dao.INlifeCoreProdDao;
import com.sq.partner.manager.dao.INlifeCoreProdRiskDao;
import com.sq.partner.manager.dao.INlifeCoreRiskCovDao;
import com.sq.partner.manager.dao.INlifeCoreRiskDao;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.NlifeCoreCov;
import com.sq.partner.manager.mybatis.model.NlifeCoreProd;
import com.sq.partner.manager.mybatis.model.NlifeCoreProdRisk;
import com.sq.partner.manager.mybatis.model.NlifeCoreRisk;
import com.sq.partner.manager.mybatis.model.NlifeCoreRiskCov;
import com.sq.partner.manager.service.INlifeCoreProdService;
import com.sq.partner.manager.util.ExcelUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.StrUtil;

@Service
public class NlifeCoreProdServiceImpl implements INlifeCoreProdService {
	private static Logger logger = LogUtil.getLog(NlifeCoreProdServiceImpl.class);

	@Resource
	private INlifeCoreProdDao nlifeCoreProdDao;
	
	@Resource
	private INlifeCoreProdRiskDao nlifeCoreProdRiskDao;
	
	@Resource
	private INlifeCoreRiskDao nlifeCoreRiskDao;
	
	@Resource
	private INlifeCoreRiskCovDao nlifeCoreRiskCovDao;
	
	@Resource
	private INlifeCoreCovDao nlifeCoreCovDao;
	
	@Override
	public List<InputStream> getFileInputStream(MultipartFile[] myfiles)
			throws AppException {
		List<InputStream> inputStreams=new ArrayList<InputStream>();
		try {
			for(MultipartFile file : myfiles){
				String fileNameStr = file.getOriginalFilename();
				int lastDotIndex = fileNameStr.lastIndexOf(".");
				if(-1==lastDotIndex){
					throw new AppException("请选择扩展名为 .xls 的文件上传");
				}else{
					String extName = fileNameStr.substring(lastDotIndex+1,fileNameStr.length());
					if(!"xls".equals(extName.toLowerCase())){
						throw new AppException("请选择扩展名为 .xls 的文件上传");
					}
				}
				if (file.isEmpty()) {
					throw new AppException("上传失败，上传文件不能为空！");
				} else {
					InputStream inputStream = file.getInputStream();
					inputStreams.add(inputStream);
				}
			}
		} catch (IOException e) {
			logger.info("上传错误!请稍后重试",e);
			throw new AppException("上传错误!请稍后重试");
		}
		return inputStreams;
	}

	@Override
	public void insertDataToDB(InputStream inputStream, GatewayUser user,HttpSession session) throws Exception {
		HSSFWorkbook workBook = null;
		int errorCount = 0;
		StringBuffer errorMsg = new StringBuffer();
		StringBuffer errorMsg2 = new StringBuffer();
		int lastRow = 0;
		List<NlifeCoreProd> list = new ArrayList<NlifeCoreProd>();
		List<NlifeCoreProdRisk> list1 = new ArrayList<NlifeCoreProdRisk>();
		List<NlifeCoreRisk> list2 = new ArrayList<NlifeCoreRisk>();
		List<NlifeCoreRiskCov> list3 = new ArrayList<NlifeCoreRiskCov>();
		List<NlifeCoreCov> list4 = new ArrayList<NlifeCoreCov>();
		try {
			workBook = new HSSFWorkbook(inputStream);
		} catch (Exception e) {
			logger.info("上传失败，上传文件内容有误", e);
			throw new AppException("上传失败，上传文件内容有误!");
		}
		for (int i = 0; i < 5; i++) {
			HSSFSheet sheet = workBook.getSheetAt(i);
			String sheetName = workBook.getSheetName(i);
			
			if (StrUtil.isNotEmpty(sheetName) && sheetName.equals("产品")) {
				if (sheet != null) {
					lastRow = sheet.getLastRowNum();
				}
				if (lastRow > 2) {
					int count = sheet.getLastRowNum();
					for (int rowNum = 3; rowNum <= count; rowNum++) {
						HSSFRow hssfRow = sheet.getRow(rowNum);
						if (hssfRow != null) {
							for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
								if ((1 != colNum)&& (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))) {
									errorMsg.append("产品信息第" + (rowNum + 1)+ " 行数据未导入，原因：列数据为必录项不允许为空，请检查 <br>");
									errorCount++;
									continue;
								}
							}
							if (errorCount > 10) {
								throw new AppException("上传失败:<br>" + errorMsg.toString());
							}
							String coreProdCode = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
							String prodSaleCode = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
							String coreProdName = ExcelUtil.getCellText(hssfRow.getCell(2)).trim();
							NlifeCoreProd nlifeCoreProd = nlifeCoreProdDao.queryById(coreProdCode);
							if (nlifeCoreProd!=null) {
								errorMsg2.append("产品信息第" + (rowNum + 1)+ " 行数据未导入，原因：产品代码重复，请检查</br>");
								continue;
							}
							NlifeCoreProd coreprod = new NlifeCoreProd();
							coreprod.setCoreProdCode(coreProdCode);
							coreprod.setProdSaleCode(prodSaleCode);
							coreprod.setCoreProdName(coreProdName);
							coreprod.setOperUser(user.getUserName());
							list.add(coreprod);
						}
					}
					if (StrUtil.isNotEmpty(errorMsg.toString())) {
						throw new AppException("上传失败:<br>" + errorMsg.toString());
					}
				} else{
					errorMsg.append("Excel内容为空 <br>");
					throw new AppException("上传失败:<br>" + errorMsg.toString());
				}
			}
			if (StrUtil.isNotEmpty(sheetName) && sheetName.equals("产品条款关联")) {
				if (sheet != null) {
					lastRow = sheet.getLastRowNum();
				}
				if (lastRow > 2) {
					int count = sheet.getLastRowNum();
					for (int rowNum = 3; rowNum <= count; rowNum++) {
						HSSFRow hssfRow = sheet.getRow(rowNum);
						if (hssfRow != null) {
							for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
								if (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim())) {
									errorMsg.append("产品条款关联信息第" + (rowNum + 1)+ " 行数据未导入，原因：列数据为必录项不允许为空，请检查 <br>");
									errorCount++;
									continue;
								}
							}
							if (errorCount > 10) {
								throw new AppException("上传失败:<br>" + errorMsg.toString());
							}
							String coreProdCode = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
							String coreRiskCode = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
							NlifeCoreProdRisk nlifeCoreProdRisk = nlifeCoreProdRiskDao.queryByProdRisk(coreProdCode,coreRiskCode);
							if (nlifeCoreProdRisk!=null) {
								errorMsg2.append("产品条款关联信息第" + (rowNum + 1)+ " 行数据未导入，原因：产品代码与条款代码重复，请检查</br>");
								continue;
							}
							NlifeCoreProdRisk coreprodrisk = new NlifeCoreProdRisk();
							coreprodrisk.setCoreProdCode(coreProdCode);
							coreprodrisk.setCoreRiskCode(coreRiskCode);
							coreprodrisk.setOperUser(user.getUserName());
							list1.add(coreprodrisk);
						}
					}
					if (StrUtil.isNotEmpty(errorMsg.toString())) {
						throw new AppException("上传失败:<br>" + errorMsg.toString());
					}
				} else{
					errorMsg.append("Excel内容为空 <br>");
					throw new AppException("上传失败:<br>" + errorMsg.toString());
				}
			}
			if (StrUtil.isNotEmpty(sheetName) && sheetName.equals("条款")) {
				if (sheet != null) {
					lastRow = sheet.getLastRowNum();
				}
				if (lastRow > 2) {
					int count = sheet.getLastRowNum();
					for (int rowNum = 3; rowNum <= count; rowNum++) {
						HSSFRow hssfRow = sheet.getRow(rowNum);
						if (hssfRow != null) {
							System.out.println(hssfRow.getLastCellNum());
							for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
								if ((2 != colNum)&&(3 != colNum)&&(6 != colNum)&&(7 != colNum)&&(8 != colNum)&&(9 != colNum)&& (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))) {
									errorMsg.append("条款信息第" + (rowNum + 1)+ " 行数据未导入，原因：列数据为必录项不允许为空，请检查 <br>");
									errorCount++;
									continue;
								}
							}
							if (errorCount > 10) {
								throw new AppException("上传失败:<br>" + errorMsg.toString());
							}
							String coreRiskCode = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
							String coreRiskName = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
							String prmryFlag = ExcelUtil.getCellText(hssfRow.getCell(2)).trim();
							String prodSaleCode = ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
							String riskType = ExcelUtil.getCellText(hssfRow.getCell(4)).trim();
							String isInternetRisk = ExcelUtil.getCellText(hssfRow.getCell(5)).trim();
							String clauseType = ExcelUtil.getCellText(hssfRow.getCell(10)).trim();
							if (!("Y".equalsIgnoreCase(isInternetRisk) || "N".equalsIgnoreCase(isInternetRisk))){
								throw new AppException("上传失败: 条款信息第" +(rowNum + 1)+ " 行数据格式错误，请检查 <br>");
							}
							BigDecimal moreYearGroup = null;
							if (StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(6)).trim())){
								moreYearGroup = new BigDecimal(ExcelUtil.getCellText(hssfRow.getCell(6)).trim());
							}
							BigDecimal moreYearSingle = null;
							if (StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(7)).trim())){
								moreYearSingle = new BigDecimal(ExcelUtil.getCellText(hssfRow.getCell(7)).trim());
							}
							BigDecimal oneYearGroup = null;
							if (StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(8)).trim())){
								oneYearGroup = new BigDecimal(ExcelUtil.getCellText(hssfRow.getCell(8)).trim());
							}
							BigDecimal oneYearSingle = null;
							if (StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(9)).trim())){
								oneYearSingle = new BigDecimal(ExcelUtil.getCellText(hssfRow.getCell(9)).trim());
							}
							if(nlifeCoreRiskDao.queryCoreRiskById(coreRiskCode)!=null){
								//重复只更新是否互联网和三费的信息
								Map<Object,Object> updateMap = new HashMap<Object, Object>();
								updateMap.put("coreRiskCode",coreRiskCode);
								updateMap.put("isInternetRisk",isInternetRisk);
								updateMap.put("moreYearSingle",moreYearSingle);
								updateMap.put("moreYearGroup",moreYearGroup);
								updateMap.put("oneYearSingle",oneYearSingle);
								updateMap.put("oneYearGroup",oneYearGroup);
								nlifeCoreRiskDao.updateNlifeCoreRiskInter(updateMap);
								errorMsg2.append("条款信息第" + (rowNum + 1)+ " 行数据未导入，原因：条款代码重复，请检查，上限信息已更新</br>");
								continue;
							}
							NlifeCoreRisk nlifeCoreRisk = new NlifeCoreRisk();
							nlifeCoreRisk.setCoreRiskCode(coreRiskCode);
							nlifeCoreRisk.setCoreRiskName(coreRiskName);
							nlifeCoreRisk.setOperUser(user.getUserName());
							//主险转换为0  附加险转换为1
							if (("主险").equals(prmryFlag)||("0").equals(prmryFlag)) {
								nlifeCoreRisk.setPrmryFlag("0");
							}else if (("附加险").equals(prmryFlag)||("1").equals(prmryFlag)) {
								nlifeCoreRisk.setPrmryFlag("1");
							}
							nlifeCoreRisk.setProdSaleCode(prodSaleCode);
							nlifeCoreRisk.setRiskType(riskType);
							nlifeCoreRisk.setIsInternetRisk(isInternetRisk);
							nlifeCoreRisk.setMoreYearGroup(moreYearGroup);
							nlifeCoreRisk.setMoreYearSingle(moreYearSingle);
							nlifeCoreRisk.setOneYearGroup(oneYearGroup);
							nlifeCoreRisk.setOneYearSingle(oneYearSingle);
							nlifeCoreRisk.setClauseType(clauseType);
							list2.add(nlifeCoreRisk);
						}
					}
					if (StrUtil.isNotEmpty(errorMsg.toString())) {
						throw new AppException("上传失败:<br>" + errorMsg.toString());
					}
				} else{
					errorMsg.append("Excel内容为空 <br>");
					throw new AppException("上传失败:<br>" + errorMsg.toString());
				}
			}
			if (StrUtil.isNotEmpty(sheetName) && sheetName.equals("条款责任关联")) {
				if (sheet != null) {
					lastRow = sheet.getLastRowNum();
				}
				if (lastRow > 2) {
					int count = sheet.getLastRowNum();
					for (int rowNum = 3; rowNum <= count; rowNum++) {
						HSSFRow hssfRow = sheet.getRow(rowNum);
						if (hssfRow != null) {
							for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
								//责任别名 可以为空
								if ((2 != colNum)&& (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))) {
									errorMsg.append("条款责任关联信息第" + (rowNum + 1)+ " 行数据未导入，原因：列数据为必录项不允许为空，请检查 <br>");
									errorCount++;
									continue;
								}
							}
							if (errorCount > 10) {
								throw new AppException("上传失败:<br>" + errorMsg.toString());
							}
							String coreRiskCode = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
							String coreCovCode = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
							String coreCovAlias = ExcelUtil.getCellText(hssfRow.getCell(2)).trim();
							String parentCovCode = ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
							if(nlifeCoreRiskCovDao.queryByRiskCov(coreRiskCode,coreCovCode)!=null){
								errorMsg2.append("条款责任关联信息第" + (rowNum + 1)+ " 行数据未导入，原因：条款代码与责任代码重复，请检查</br>");
								continue;
							}
							NlifeCoreRiskCov nlifeCoreRiskCov = new NlifeCoreRiskCov();
							nlifeCoreRiskCov.setCoreRiskCode(coreRiskCode);
							nlifeCoreRiskCov.setCoreCovCode(coreCovCode);
							nlifeCoreRiskCov.setCoreCovAlias(coreCovAlias);
							nlifeCoreRiskCov.setParentCovCode(parentCovCode);
							nlifeCoreRiskCov.setOperUser(user.getUserName());
							list3.add(nlifeCoreRiskCov);
						}
					}
					if (StrUtil.isNotEmpty(errorMsg.toString())) {
						throw new AppException("上传失败:<br>" + errorMsg.toString());
					}
				} else{
					errorMsg.append("Excel内容为空 <br>");
					throw new AppException("上传失败:<br>" + errorMsg.toString());
				}
			}
			if (StrUtil.isNotEmpty(sheetName) && sheetName.equals("责任")) {
				if (sheet != null) {
					lastRow = sheet.getLastRowNum();
				}
				if (lastRow > 2) {
					int count = sheet.getLastRowNum();
					for (int rowNum = 3; rowNum <= count; rowNum++) {
						HSSFRow hssfRow = sheet.getRow(rowNum);
						if (hssfRow != null) {
							for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
								//责任别名 可以为空
//								if (colNum == 2){
//									continue;
//								}
								if (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim())) {
									errorMsg.append("责任信息第" + (rowNum + 1)+ " 行数据未导入，原因：列数据为必录项不允许为空，请检查 <br>");
									errorCount++;
									continue;
								}
							}
							if (errorCount > 10) {
								throw new AppException("上传失败:<br>" + errorMsg.toString());
							}
							String coreCovCode = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
							String coreCovName = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
							String coreCovLevel = ExcelUtil.getCellText(hssfRow.getCell(2)).trim();
							String covCodeStart = coreCovCode.substring(0, 1);
							if (!covCodeStart.equals(coreCovLevel)) {
								errorMsg.append("责任信息第" + (rowNum + 1)+ " 行数据未导入，原因：一级或二级责任和责任等级必须一致，请检查 <br>");
								errorCount++;
								continue;
							}
							if (nlifeCoreCovDao.queryById(coreCovCode)!=null) {
								errorMsg2.append("责任信息第" + (rowNum + 1)+ " 行数据未导入，原因：责任代码重复，请检查</br>");
								continue;
							}
							NlifeCoreCov nlifeCoreCov = new NlifeCoreCov();
							nlifeCoreCov.setCoreCovCode(coreCovCode);
							nlifeCoreCov.setCoreCovName(coreCovName);
							nlifeCoreCov.setCoreCovLevel(Integer.parseInt(coreCovLevel));
							nlifeCoreCov.setOperUser(user.getUserName());
							list4.add(nlifeCoreCov);
						}
					}
					if (StrUtil.isNotEmpty(errorMsg.toString())) {
						throw new AppException("上传失败:<br>" + errorMsg.toString());
					}
				} else{
					errorMsg.append("Excel内容为空 <br>");
					throw new AppException("上传失败:<br>" + errorMsg.toString());
				}
			}
		}
		if (list.size()> 0) {
			nlifeCoreProdDao.insertCoreProds(list);
		}
		if (list1.size()> 0) {
			nlifeCoreProdRiskDao.insertCoreProdRisks(list1);
		}
		if (list2.size()> 0) {
			nlifeCoreRiskDao.insertCoreRisks(list2);
		}
		if (list3.size()> 0) {
			nlifeCoreRiskCovDao.insertCoreRiskCovs(list3);
		}
		if (list4.size()> 0) {
			nlifeCoreCovDao.insertCoreCovs(list4);
		}
		if (StrUtil.isNotEmpty(errorMsg2.toString())) {
			throw new AppException("上传完毕:<br>" + errorMsg2.toString());
		}else{
			session.setAttribute("result", "新意健险核心产品导入成功");
		}
	}
}
